package com.wwl.base.common.excel;

import com.wwl.base.common.excel.document.DocumentFactory;
import com.wwl.base.common.excel.document.XlsDocumentFactory;
import com.wwl.base.common.excel.document.XlsxDocumentFactory;
import com.wwl.base.common.excel.json.JSONArray;
import com.wwl.base.common.excel.json.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @author wwl
 * @version 1.0
 * @date 2021/2/8 11:19
 * @description:
 */
public class ExcelConverter {
    private DocumentFactory documentFactory;


    private ExcelConverter(DocumentFactory documentFactory) {
        this.documentFactory = documentFactory;
    }

    //==============================静态方法====================================

    public static ExcelConverter forXls() {
        return new ExcelConverter(XLS_DOCUMENT_FACTORY);
    }

    public static ExcelConverter forXlsx() {
        return new ExcelConverter(XLSX_DOCUMENT_FACTORY);
    }

    private static final XlsDocumentFactory XLS_DOCUMENT_FACTORY = new XlsDocumentFactory();

    private static final XlsxDocumentFactory XLSX_DOCUMENT_FACTORY = new XlsxDocumentFactory();


    //==============================序列化/反序列化实现====================================

    public JSONArray parse(InputStream stream, DataTable config) throws IOException {
        Workbook document  = documentFactory.getReadableWorkbook(stream);
        return parse(document, config);
    }

    public Map<String, JSONArray> parse(InputStream stream, List<DataTable> configs) throws IOException {

        Map<String, JSONArray> result = new HashMap<>();
        Workbook document = documentFactory.getReadableWorkbook(stream);
        for (DataTable config : configs) {
            JSONArray list = parse(document, config);
            result.put(config.getTableName(), list);
        }

        return result;
    }

    public JSONArray parse(Workbook document, DataTable config) {
        Sheet sheet = document.getSheet(config.getTableName());
        if (sheet == null) return new JSONArray();
        Map<Integer, String> columnNames = new HashMap<>();

        //默认第一行为表头
        Row firstRow = sheet.getRow(sheet.getFirstRowNum());
        for (int colIdx = 0; colIdx < firstRow.getLastCellNum(); colIdx++) {
            Cell cell = firstRow.getCell(colIdx);
            Object value = getCellValue(cell);
            if (value == null) continue;
            String cellValue = String.valueOf(value).trim();

            //按照displayName匹配name
            for (DataColumn column : config.getColumns()) {
                String displayName = column.getShowName();
                if (cellValue.equals(displayName)) {
                    String columnName = column.getFieldName();
                    columnNames.put(colIdx, columnName);
                }
            }
        }

        //读取数据
        JSONArray list = new JSONArray();
        for (int rowIdx = 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            try {
                JSONObject rowData = new JSONObject();
                for (int colIdx = 0; colIdx < row.getLastCellNum(); colIdx++) {
                    try {
                        if (columnNames.containsKey(colIdx)) {
                            Cell cell = row.getCell(colIdx);
                            Object value = getCellValue(cell);

                            String columnName = columnNames.get(colIdx);
                            rowData.put(columnName, value);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                boolean allFieldIsNullOrEmpty = rowData.values().stream()
                        .allMatch(value ->
                                value == null || !StringUtils.hasText(value.toString())
                        );
                if (!allFieldIsNullOrEmpty) {
                    list.add(rowData);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public void export(OutputStream stream, DataTable config, JSONArray list) throws IOException {
        Assert.notNull(list);
        Assert.notNull(config);

        //create excel document and sheet
        Workbook document = documentFactory.getWriteableWorkbook();
        export(document, config, list);

        //write document to stream
        document.write(stream);
    }

    public void export(OutputStream stream, List<DataTable> configs, Map<String, JSONArray> data) throws IOException {
        Assert.notNull(data);
        Assert.notNull(configs);

        //create excel document and sheet
        Workbook document = documentFactory.getWriteableWorkbook();
        for (DataTable config : configs) {
            String name = config.getTableName();
            JSONArray list = data.getOrDefault(name, new JSONArray());
            export(document, config, list);
        }

        //write document to stream
        document.write(stream);
    }

    public void export(Workbook document, DataTable config, JSONArray list) {
        Assert.notNull(list);
        Assert.notNull(config);
        Sheet sheet = document.createSheet(config.getTableName());
        //write header
        Row firstRow = sheet.createRow(0);
        for (int colIdx = 0; colIdx < config.getColumns().size(); colIdx++) {

            //设置列数据类型为text
            CellStyle defaultStyle = document.createCellStyle();
            defaultStyle.setDataFormat(document.createDataFormat().getFormat("@"));
            sheet.setDefaultColumnStyle(colIdx, defaultStyle);

            //设置标题栏样式
            Font headerFont = document.createFont();
            headerFont.setFontHeightInPoints((short) 16);
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

            CellStyle headerStyle = document.createCellStyle();
            headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
            headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            headerStyle.setFont(headerFont);

            DataColumn column = config.getColumns().get(colIdx);
            String columnDisplayName = column.getShowName();

            Cell cell = firstRow.createCell(colIdx);
            cell.setCellValue(columnDisplayName);
            cell.setCellStyle(headerStyle);
        }
        //write data_source list
        for (int rowIdx = 0; rowIdx < list.size(); rowIdx++) {
            Row row = sheet.createRow(rowIdx + 1);
            JSONObject rowData = list.getJSONObject(rowIdx);
            for (int colIdx = 0; colIdx < config.getColumns().size(); colIdx++) {
                DataColumn column = config.getColumns().get(colIdx);
                String cellValue = rowData.getString(column.getFieldName());

                Cell cell = row.createCell(colIdx);
                cell.setCellValue(cellValue);
            }
        }
        //auto resize column witdh
        for (int colIdx = 0; colIdx < config.getColumns().size(); colIdx++) {
            sheet.setColumnWidth(colIdx, 20 * 256);
        }
    }

    private Object getCellValue(Cell cell) {
        if (cell == null) return null;

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return DateUtil.getJavaDate(cell.getNumericCellValue());
                } else {
                    return cell.getNumericCellValue();
                }
            case Cell.CELL_TYPE_STRING: // 字符串
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                return cell.getBooleanCellValue();
            case Cell.CELL_TYPE_FORMULA: // 公式
                return cell.getCellFormula();
            case Cell.CELL_TYPE_BLANK: // 空值
                return "";
            case Cell.CELL_TYPE_ERROR: // 故障
                return null;
            default:
                break;
        }
        return null;
    }
}
